<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://www.w3.org/TR/html4/loose.dtd">
<html lang="zh-CN"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta http-equiv="Content-Language" content="zh-CN"><link href="stylesheet.css" media="all" rel="stylesheet" type="text/css">
<title>从 PL/Perl 访问数据库</title>
<script> var _hmt = _hmt || []; (function() { var hm = document.createElement("script"); hm.src = "https://hm.baidu.com/hm.js?d286c55b63a3c54a1e43d10d4c203e75"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(hm, s); })(); </script>
</head><body class="SECT1">
<div>
<table summary="Header navigation table" width="100%" border="0" cellpadding="0" cellspacing="0">
<tr><th colspan="5" align="center" valign="bottom">PostgreSQL 8.2.3 中文文档</th></tr>
<tr><td width="10%" align="left" valign="top"><a href="plperl-funcs.html" accesskey="P">后退</a></td><td width="10%" align="left" valign="top"><a href="plperl.html">快退</a></td><td width="60%" align="center" valign="bottom">章39. PL/Perl - Perl 过程语言</td><td width="10%" align="right" valign="top"><a href="plperl.html">快进</a></td><td width="10%" align="right" valign="top"><a href="plperl-data.html" accesskey="N">前进</a></td></tr>
</table>
<hr align="LEFT" width="100%"></div>
<div class="SECT1"><h1 class="SECT1"><a name="PLPERL-DATABASE">39.2. 从 PL/Perl 访问数据库</a></h1>
<p>从 Perl 函数里访问数据库本身可以通过下面描述的 <code class="FUNCTION">spi_exec_query</code> 或者一个试验模块 <a href="http://www.cpan.org/modules/by-module/DBD/APILOS/" target="_top"><tt class="LITERAL">DBD::PgSPI</tt></a>(也可以在 <a href="http://www.cpan.org/SITES.html" target="_top">CPAN 镜像站</a>获取)。这个模块让用户可以使用一个 DBI 兼容的叫 <tt class="VARNAME">$pg_dbh</tt> 的数据库句柄，用户可以用它以普通的 DBI 语法执行查询。</p>
<p>PL/Perl 本身目前只提供四个额外的 Perl 命令：</p>
<div class="VARIABLELIST">
<dl>
<dt><tt class="LITERAL"><code class="FUNCTION">spi_exec_query</code>(<tt class="REPLACEABLE"><i>query</i></tt> [, <tt class="REPLACEABLE"><i>max-rows</i></tt>])</tt><br><tt class="LITERAL"><code class="FUNCTION">spi_query</code>(<tt class="REPLACEABLE"><i>command</i></tt>)</tt><br><tt class="LITERAL"><code class="FUNCTION">spi_fetchrow</code>(<tt class="REPLACEABLE"><i>cursor</i></tt>)</tt><br><tt class="LITERAL"><code class="FUNCTION">spi_prepare</code>(<tt class="REPLACEABLE"><i>command</i></tt>, <tt class="REPLACEABLE"><i>argument types</i></tt>)</tt><br><tt class="LITERAL"><code class="FUNCTION">spi_exec_prepared</code>(<tt class="REPLACEABLE"><i>plan</i></tt>)</tt><br><tt class="LITERAL"><code class="FUNCTION">spi_query_prepared</code>(<tt class="REPLACEABLE"><i>plan</i></tt> [, <tt class="REPLACEABLE"><i>attributes</i></tt>], <tt class="REPLACEABLE"><i>arguments</i></tt>)</tt><br><tt class="LITERAL"><code class="FUNCTION">spi_cursor_close</code>(<tt class="REPLACEABLE"><i>cursor</i></tt>)</tt><br><tt class="LITERAL"><code class="FUNCTION">spi_freeplan</code>(<tt class="REPLACEABLE"><i>plan</i></tt>)</tt></dt>
<dd><p><tt class="LITERAL">spi_exec_query</tt> 执行一个 SQL 命令然后把整个结果集当作一个指向散列引用的引用返回。<span class="emphasis"><i class="EMPHASIS">只有在你知道结果集相对比较小的时候才能用这个命令。</i></span>下面是一个带有额外的最大行数的查询(<tt class="COMMAND">SELECT</tt> 命令)的例子。</p>
<pre class="PROGRAMLISTING">$rv = spi_exec_query('SELECT * FROM my_table', 5);</pre>
<p>它从 <tt class="LITERAL">my_table</tt> 里返回最多 5 行。如果 <tt class="LITERAL">my_table</tt>有一个字段是 <tt class="LITERAL">my_column</tt> ，那么可以用下面的方法从结果的第 <tt class="LITERAL">$i</tt> 行获取其值：</p>
<pre class="PROGRAMLISTING">$foo = $rv-&gt;{rows}[$i]-&gt;{my_column};</pre>
<p>从一个 <tt class="COMMAND">SELECT</tt> 查询返回的总行数可以这样访问：</p>
<pre class="PROGRAMLISTING">$nrows = $rv-&gt;{processed}</pre>
<p>这里是一个使用其它命令的例子：</p>
<pre class="PROGRAMLISTING">$query = "INSERT INTO my_table VALUES (1, 'test')";
$rv = spi_exec_query($query);</pre>
<p>你可以用下面方法访问状态(<tt class="LITERAL">SPI_OK_INSERT</tt>)：</p>
<pre class="PROGRAMLISTING">$res = $rv-&gt;{status};</pre>
<p>这样获取影响的行数：</p>
<pre class="PROGRAMLISTING">$nrows = $rv-&gt;{processed};</pre>
<p>下面是一个完整的例子：</p>
<pre class="PROGRAMLISTING">CREATE TABLE test (
    i int,
    v varchar
);

INSERT INTO test (i, v) VALUES (1, 'first line');
INSERT INTO test (i, v) VALUES (2, 'second line');
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');

CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
    my $rv = spi_exec_query('select i, v from test;');
    my $status = $rv-&gt;{status};
    my $nrows = $rv-&gt;{processed};
    foreach my $rn (0 .. $nrows - 1) {
        my $row = $rv-&gt;{rows}[$rn];
        $row-&gt;{i} += 200 if defined($row-&gt;{i});
        $row-&gt;{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row-&gt;{v}));
        return_next($row);
    }
    return undef;
$$ LANGUAGE plperl;

SELECT * FROM test_munge();</pre>
<p><tt class="LITERAL">spi_query</tt> 和 <tt class="LITERAL">spi_fetchrow</tt> 一起用于处理那些行集可能比较大，或者是在你收到行的时候就返回的场合。<tt class="LITERAL">spi_fetchrow</tt> <span class="emphasis"><i class="EMPHASIS">只能</i></span>和 <tt class="LITERAL">spi_query</tt> 一起使用。下面的例子演示了如何使用：</p>
<pre class="PROGRAMLISTING">CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);

CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
    use Digest::MD5 qw(md5_hex);
    my $file = '/usr/share/dict/words';
    my $t = localtime;
    elog(NOTICE, "opening file $file at $t" );
    open my $fh, '&lt;', $file # 这是访问文件！
        or elog(ERROR, "can't open $file for reading: $!");
    my @words = &lt;$fh&gt;;
    close $fh;
    $t = localtime;
    elog(NOTICE, "closed file $file at $t");
    chomp(@words);
    my $row;
    my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
    while (defined ($row = spi_fetchrow($sth))) {
        return_next({
            the_num =&gt; $row-&gt;{a},
            the_text =&gt; md5_hex($words[rand @words])
        });
    }
    return;
$$ LANGUAGE plperlu;

SELECT * from lotsa_md5(500);</pre>
<p><tt class="LITERAL">spi_prepare</tt>, <tt class="LITERAL">spi_query_prepared</tt>, <tt class="LITERAL">spi_exec_prepared</tt>, <tt class="LITERAL">spi_freeplan</tt> 为预备查询实现同样的功能。一旦一个查询规划通过调用 <tt class="LITERAL">spi_prepare</tt> 准备好，该规划就可以代替查询字符串，不管是在 <tt class="LITERAL">spi_exec_prepared</tt> 中(与 <tt class="LITERAL">spi_exec_query</tt> 返回的结果相同)还是在 <tt class="LITERAL">spi_query_prepared</tt> 中(与 <tt class="LITERAL">spi_query</tt> 返回的游标相同)，之后可以被传递给 <tt class="LITERAL">spi_fetchrow</tt> 。</p>
<p>预备查询的好处是可以为多个查询的执行使用一个预备规划。在规划不在被需要之后，可以通过 <tt class="LITERAL">spi_freeplan</tt> 释放：</p>
<pre class="PROGRAMLISTING">CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $$
        $_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL');
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
        return spi_exec_prepared(
                $_SHARED{my_plan},
                $_[0],
        )-&#62;{rows}-&#62;[0]-&#62;{now};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION done() RETURNS INTEGER AS $$
        spi_freeplan( $_SHARED{my_plan});
        undef $_SHARED{my_plan};
$$ LANGUAGE plperl;

SELECT init();
SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
SELECT done();

  add_time  |  add_time  |  add_time
------------+------------+------------
 2005-12-10 | 2005-12-11 | 2005-12-12
    </pre>
<p>注意，<tt class="LITERAL">spi_prepare</tt> 中的参数是通过 $1, $2, $3 ... 表示的，因此避免在双引号中声明查询字符串可能会导致难以发现的臭虫。</p>
<p>通常，应当重复 <code class="FUNCTION">spi_fetchrow</code> 直到其返回 <tt class="LITERAL">undef</tt> 以表示没有行可以读取了，此时游标将被自动释放。如果你确实不想读取所有行，可以明确调用 <code class="FUNCTION">spi_cursor_close</code> 来释放游标，否则将会导致内存泄漏。</p></dd>
<dt><tt class="LITERAL"><code class="FUNCTION">elog</code>(<tt class="REPLACEABLE"><i>level</i></tt>, <tt class="REPLACEABLE"><i>msg</i></tt>)</tt></dt>
<dd><p>发出一条日志或者错误信息。可能的级别是 <tt class="LITERAL">DEBUG</tt>, <tt class="LITERAL">LOG</tt>, <tt class="LITERAL">INFO</tt>, <tt class="LITERAL">NOTICE</tt>, <tt class="LITERAL">WARNING</tt>, <tt class="LITERAL">ERROR</tt> 。<tt class="LITERAL">ERROR</tt> 抛出一个错误条件。如果这个错误没有被周围的 Perl 代码捕获，那么错误将传播到调用的查询里，导致当前事务或者子事务退出。这实际上相当于 Perl 的 <tt class="LITERAL">die</tt> 命令。其它级别只是生成不同优先级的消息。特定优先级的消息是否报告给客户端、写到服务器日志、或者两个都做，是由配置参数 <a href="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES">log_min_messages</a> 和 <a href="runtime-config-logging.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> 控制的。参阅<a href="runtime-config.html">章17</a>获取更多信息。</p></dd>
</dl>
</div>
</div>
<div>
<hr align="LEFT" width="100%">
<table summary="Footer navigation table" width="100%" border="0" cellpadding="0" cellspacing="0">
<tr><td width="33%" align="left" valign="top"><a href="plperl-funcs.html" accesskey="P">后退</a></td><td width="34%" align="center" valign="top"><a href="index.html" accesskey="H">首页</a></td><td width="33%" align="right" valign="top"><a href="plperl-data.html" accesskey="N">前进</a></td></tr>
<tr><td width="33%" align="left" valign="top">PL/Perl 函数和参数</td><td width="34%" align="center" valign="top"><a href="plperl.html" accesskey="U">上一级</a></td><td width="33%" align="right" valign="top">PL/Perl 里的数据值</td></tr>
</table>
</div>
</body></html>